Microsoft Access 2016 Essential Training: Best Database Training by Matthew Park

Author:Matthew Park [Park, Matthew]
Language: eng
Format: azw3, epub
Tags: Best Database Training
Publisher: Smartebookshop.com
Published: 2018-11-24T16:00:00+00:00

Getting a different look with crosstab queries

Queries generally follow the rows and columns, or records and fields model for their layout. We have a slightly modified layout option available to us, called a Crosstab Query, that gives us headers along the top and side and then aggregate, or summary, data that appears in the middle. Let us put one together that looks at the visits to our various room options at the hotel. Now, the Crosstab Query is just a variation on a Totals Query, so we will start there, and then convert it over to a Crosstab Query. To create that Totals Query, we start in the Create tab and go ahead and visit the Query Designer here.

Now, I am going to add in some information from my RoomAssignments table here, and then I also want the Rooms data, so that I can get some information about the types of beds that we have available in our rooms. Go ahead and close the Show Table window here. And I am going to double-click on RoomName and Bed, to add both of those columns down here into my query grid. And then I also want to pull out the AssignmentID, which is at the bottom of the RoomAssignments table. This will allow me to provide a count of how many different RoomAssignments there were in this room with a bed configuration.

If we turn it into a Totals Query by pressing this button up here on the ribbon, we get the new Total row here. So, I want to group my values based off the RoomName and the Bed type, and then for the AssignmentID, we are going to go ahead and count the number of assignments that occurred within each of these groups. Let us go ahead and run that Query, so we can see the results so far. And you can see that we have several different rooms listed here with their bed options, and then we get a count of how many times a person visited that combination of room and bed. Let us go ahead and switch it back into Design View, and we can turn this Query into a Crosstab Query by coming over here to the Query Type section and pressing the Crosstab Query button here.

That will add in a new row down here on the bottom, called Crosstab, and if you click in here, you will get a dropdown menu, and we have some options for how we want our Crosstab Query to lay out. We have Row Heading, Column Heading, and then Value, so we need to supply at least three columns for a successful Crosstab Query. Let us go ahead and assign the Row Heading to the RoomName column. I will come over here to Bed. I am going to change that to the Column Heading, so that will be across the top. And then the AssignmentID is going to be the Value. The Value will show up in the middle, or the intersection, between each of our RoomNames and Beds.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.